package cn.tedu.boot02.controller;

import cn.tedu.boot02.entity.Product;
import cn.tedu.boot02.utils.DBUtil;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author 作者：HeShaoShuai
 * @Email 邮箱：2569819541@qq.com
 * @Date 日期：2023/5/11 8:16
 * @Package 包名：cn.tedu.boot02.controller
 * @Project 项目名：JavaSE
 * @Version 1.0
 */
//使用此注解 相当于在每一个方法上面都添加了一个@ResponseBody
@RestController
public class ProductController {

	@RequestMapping("/insert")
	public String insert(Product product){
		System.out.println("product = " + product);
		//通过jdbc代码把接收到的数据保存到数据库里面
		try (Connection conn = DBUtil.getConnection()){
			//准备执行插入数据的SQL语句
			String sql = "INSERT INTO product VALUES(NULL,?,?,?)";
			//创建执行SQL语句的对象
			PreparedStatement ps = conn.prepareStatement(sql);
			//替换?的内容
			ps.setString(1,product.getTitle());
			ps.setDouble(2,product.getPrice());
			ps.setInt(3,product.getNum());
			//执行SQL语句
			ps.executeUpdate();
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return "添加完成!<a href='/'>返回首页</a>";
	}

	@RequestMapping("/select")
	public String select(){
		ArrayList<Product> list = new ArrayList<>();
		try (Connection conn = DBUtil.getConnection()){
			String sql = "SELECT * FROM product";
			PreparedStatement ps = conn.prepareStatement(sql);
			//执行查询 得到结果集对象
			ResultSet rs = ps.executeQuery();
			//遍历结果集对象
			while(rs.next()){
				int id = rs.getInt(1);
				String title = rs.getString(2);
				double price = rs.getDouble(3);
				int num = rs.getInt(4);
				//创建商品对象封装查询到的数据
				Product p = new Product();
				p.setId(id);
				p.setTitle(title);
				p.setPrice(price);
				p.setNum(num);
				//把商品对象添加到集合中
				list.add(p);
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		//把list集合中的数据装进表格标签
		String html = "<table border='1'>";
		html+="<caption>商品列表</caption>";
		html+="<tr><th>id</th><th>标题</th><th>价格</th><th>库存</th><th>操作</th></tr>";
		for (Product p: list) {
			html+="<tr>";
			html+="<td>"+p.getId()+"</td>";
			html+="<td>"+p.getTitle()+"</td>";
			html+="<td>"+p.getPrice()+"</td>";
			html+="<td>"+p.getNum()+"</td>";
			// http://localhost:8080/delete?id=xxx    "aaa"+x+"bbb"
			html+="<td><a href='/delete?id="+p.getId()+"'>删除</a></td>";
			html+="</tr>";
		}
		html+="</table>";
		return html;
	}

	@RequestMapping("/delete")
	public String delete(int id){
		System.out.println("id = " + id);
		try (Connection conn = DBUtil.getConnection()){
			String sql = "DELETE FROM product WHERE id=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1,id);
			ps.executeUpdate();
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return "删除成功!<a href='/select'>返回列表页面</a>";
	}

	@RequestMapping("/update")
	public String update(Product product){
		System.out.println("product = " + product);
		try (Connection conn = DBUtil.getConnection()){
			String sql = "UPDATE product SET title=?,price=?,num=? WHERE id=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1,product.getTitle());
			ps.setDouble(2,product.getPrice());
			ps.setInt(3,product.getNum());
			ps.setInt(4,product.getId());
			ps.executeUpdate();
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return "修改完成!<a href='/select'>返回列表页面</a>";
	}
}